///*
// * To change this template, choose Tools | Templates
// * and open the template in the editor.
// */
package DataBase;
//

import Entities.*;
import java.sql.*;
import java.text.DateFormatSymbols;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;

//
///**
// *
// * @author Antonio Rebelo
// */
public class DBConnection {

    private static Connection conn = null;
    private static long contador = 0;

    public DBConnection() {

        try {
            String userName = "root";
            String password = "root";
            String url = "jdbc:mysql://localhost/spamdb?allowMultiQueries=true";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            //System.out.println("Database connection established");
        } catch (Exception e) {
            //System.err.println("Cannot connect to database server");
        }
    }

    public void closeAll() {
        if (conn != null) {
            try {
                conn.close();
                // System.out.println("Database connection terminated");
            } catch (Exception e) { /*
                 * ignore close errors
                 */ }
        }
    }

    public static void executeSQL(String sqlCode) {
        try {
            Statement s = (Statement) conn.createStatement();
            s.executeUpdate(sqlCode);
            s.close();
        } catch (SQLException e) {
            System.out.println(sqlCode);
            e.printStackTrace();
            System.out.println(sqlCode);
        }
    }

    public static ResultSet selectStatement(String sqlCode) {
        ResultSet rs = null;
        try {
            Statement s = conn.createStatement();
            s.executeQuery(sqlCode);
            rs = s.getResultSet();
        } catch (Exception e) {
        }
        return rs;
    }

    public static void updateSpam(int id, int spam) {
        String sql = "UPDATE mails SET spam = " + spam + " WHERE id= " + id;
        //System.out.println(sql);
        executeSQL(sql);
    }

    public static void insertMail(Mail m) {
        Header h = m.getHeader();
        Content c = m.getContent();
        int id = 1;
        String sql = "INSERT INTO Mails "
                + "(spam, FromMailAddress, FromMailDomain, FromMailTopDomain, FromAuthor, SenderMailAddress, SenderMailDomain,"
                + " SenderMailTopDomain, SenderAuthor, ReplyToMailAddress, ReplyToMailDomain, ReplyToMailTopDomain, ReplyToAuthor,"
                + " ReturnPath, ReturnPathDomain, ReturnPathTopDomain, SentMonth, SentHour, Subject, CapsLong, CapsAvg, CapsTotal, ImgCounter,"
                + " WordCounter, LineCounter, LinkCounter, CharactersSpacesHtml, CharactersNoSpacesHtml, CharactersSpacesNoHtml,"
                + " CharactersNoSpacesNoHtml) VALUES (";

        sql += m.getSpam() + ", '" + h.getFromMailAddress() + "', '" + h.getFromMailDomain() + "', '" + h.getFromMailTopDomain() + "', '" + h.getFromAuthor() + "', '" + h.getSenderMailAddress() + "', '" + h.getSenderMailDomain() + "', '";
        sql += h.getSenderMailTopDomain() + "', '" + h.getSenderAuthor() + "', '" + h.getReplyToMailAddress() + "', '" + h.getReplyToMailDomain() + "', '" + h.getReplyToMailTopDomain() + "', '" + h.getReplyToAuthor() + "', '";
        sql += h.getReturnPath() + "', '" + h.getReturnPathDomain() + "', '" + h.getReturnPathTopDomain() + "', '";

        GregorianCalendar gc = h.getSentDate();
        sql += getMonthFromInt(h.getSentDate().get(GregorianCalendar.MONTH)) + "', ";
        sql += h.getSentDate().get(GregorianCalendar.HOUR_OF_DAY) + ", '";

        sql += h.getSubject() + "', " + c.getCapsLong() + ", " + c.getCapsAvg() + ", " + c.getCapsTotal() + ", " + c.getImgCounter() + ", ";
        sql += c.getWordCounter() + ", " + c.getLineCounter() + ", " + c.getLinkCounter() + "," + c.getCharactersSpacesHtml() + ", " + c.getCharactersNoSpacesHtml() + ", " + c.getCharactersSpacesNoHtml() + ", "
                + c.getCharactersNoSpacesNoHtml() + ");";
        //System.out.println(sql);
        executeSQL(sql);

        ResultSet rs = selectStatement("SELECT max(id) as ultimo FROM Mails");
        try {
            rs.next();
            id = rs.getInt("ultimo");
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }

        insertWords(c, m.getSpam(), id);
        insertLinks(c.getLinks(), m.getSpam(), id);

    }

    public static void insertLinks(TreeMap<String, Link> links, int spam, int idMail) {
        String sql = "";
        for (Link l : links.values()) {
            sql += "CALL insertlink('" + l.getLinkDomain() + "'," + spam + ");";
            sql += "INSERT INTO links_content VALUES ('" + l.getLinkDomain() + "','" + idMail + "'," + l.getCounter() + ");";
        }
        if (links.size() > 0) {
            executeSQL(sql);
        }
    }

    public static void calcTrainingProbabilities() {
        executeSQL("calcProbabilitiesWords()");
        //executeSQL("calcProbabilitiesWordsCombination()");
        executeSQL("calcProbabilitiesLinks()");
    }

    public static void insertWords(Content c, int spam, int id) {
        String sql = "";
        String atual;
        ArrayList<Word> lwords = c.getWordArrayList();
        ArrayList<String> aux;
        HashMap<String, ArrayList<String>> wordCombinations = new HashMap<String, ArrayList<String>>();
        if (!lwords.isEmpty()) {
            int i;

            //inserir palavras e a ligaçao delas ao mail
            for (Word w : c.getWords().values()) {
                sql += "CALL insertWord('" + w.getWord() + "'," + spam + ");";
                sql += "INSERT INTO Words_Content VALUES ('" + w.getWord() + "'," + id + "," + w.getCounter() + ");";
            }

            executeSQL(sql);


//            //criar as lista de combinacoes
//            String anterior = lwords.get(0).getWord();
//            for (i = 1; i < lwords.size(); i++) {
//                atual = lwords.get(i).getWord();
//                if(wordCombinations.containsKey(anterior))
//                    wordCombinations.get(anterior).add(atual);
//                else wordCombinations.put(anterior, new ArrayList<String>());
//            }
//            //inserir combinacoes
//            sql="";
//            for(String wo : wordCombinations.keySet()){
//                i=0;
//                aux = wordCombinations.get(wo);
//                while(i<aux.size())
//                    sql+="CALL insertWordsCombination('" + wo + "','" + aux.get(i) + "'," + spam + ","+id+");";
//            }
//            
//            executeSQL(sql);
        }
    }

    public static String getMonthFromInt(int m) {
        String month = "invalid";
        DateFormatSymbols dfs = new DateFormatSymbols();
        String[] months = dfs.getMonths();
        if (m >= 0 && m <= 11) {
            month = months[m];
        }
        return month;
    }
    //public static String 
//    public static void insertWords(HashMap<String, Word> words) {
//        for (Word w : words.values()) {
//            executeSQL("INSERT INTO Words VALUES ('" + w.getWord() + "'," + w.getCountBad() + "," + w.getCountGood() + ")");
//        }
//    }
//
//    public static HashMap<String, Word> loadWords() {
//        int totalGood, totalBad;
//        HashMap<String, Word> words = new HashMap<String, Word>();
//        String word;    // The String itself
//        int countBad;   // The total times it appears in "bad" messages
//        int countGood;
//
//        try {
//
//            ResultSet rs = selectStatement("SELECT sum(badCounter) AS totalBad,sum(goodCounter) AS totalGood FROM Words");
//            rs.next();
//            totalBad = rs.getInt("totalBad");
//            totalGood = rs.getInt("totalGood");
//
//            rs = selectStatement("SELECT * FROM Words");
//            while (rs.next()) {
//                word = rs.getString("word");
//                countBad = rs.getInt("badCounter");
//                countGood = rs.getInt("goodCounter");
//
//                words.put(word, new Word(word, countBad, countGood, countBad / totalBad, countGood / totalGood));
//            }
//        } catch (Exception ex) {
//        }
//        return words;
//    }
}
